﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Diagnostics;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Net;
using System.Net.Mail;
using System.Net.Mime;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Runtime.Serialization.Formatters.Binary;
using ConfigLibrary;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using HtmlAgilityPack;
using System.Text.RegularExpressions;

namespace Trident
{
  public partial class trident : mysqlcon
  {
    
    
    public trident()
    {
      tracer.Trace(traceout, "init");
      InitializeComponent();
      emailpassword();
    }

    public void trident_Load(object sender, EventArgs e)
    {
      tracer.Trace(traceout, string.Format("connecting to database"));
      connecttrident(); tracer.Trace(traceout, "loading trident database");
      loadmysqltrident(tricmd, trida);
      loadtables("p");
      disconnect();
      #if (!DEBUG)
      tracer.Trace(traceout, "~~ web browser start ~~");
      tridexp.AllowNavigation = true;
      tridexp.Navigate(new Uri("https://wmt.quickbase.com/db/bfngn7tvg?a=q&qid=1002789&qrppg=1000"), false);  //this is the RHA Trident Report
      #elif (DEBUG)
      tracer.Trace(traceout, "~~DEBUG:: web browser start ~~");
      tridexp.AllowNavigation = true;
      tridexp.Navigate(new Uri("http://winxp134b.rha.local/qbtrident.html"), false);  //this is the RHA Trident Report
      #endif
    }

    public void emailpassword()
    {
      Stream s = File.Open(Properties.Settings.Default.CONFIGTEST, FileMode.Open);
      BinaryFormatter b = new BinaryFormatter();
      Config config = (Config)b.Deserialize(s);
      s.Close(); tracer.Trace(traceout, "config loaded");
      config.GetQbInfo(out _qbusername, out _qbpass); tracer.Trace(traceout, string.Format("qbuser: {0} ~ qbpass: {1}",_qbusername, _qbpass));
    }
    
    private void tridexp_DocumentCompleted(object sender, WebBrowserDocumentCompletedEventArgs z)
    {
      DateTime today = DateTime.Today;
      DateTime date = new DateTime();
      DateTime passwordexpired = new DateTime();
      //System.IO.File.WriteAllText(tracer.SaveLocation+"qbpage.html", tridexp.DocumentText);
      //loggin = Inject_ExecuteJavaScript(tridexp.Document);

      /*
       * Set `loggin` true to skip athentication during DEBUG
       */
      #if (DEBUG)
      loggin = true; tracer.Trace(traceout, "DEBUG:: skipping login");
      #endif
      if (loggin == false)
      {
        try
        {
          HtmlElement email = tridexp.Document.GetElementById("loginid");
          email.SetAttribute("value", _qbusername);
          HtmlElement password = tridexp.Document.GetElementById("password");
          pass = contacts.Tables["p"];
          
          int prow = pass.Rows.Count;
          for (int p = 0; p < prow; p++)
          {
            if (!String.IsNullOrEmpty(pass.Rows[p]["Date"].ToString()) && String.IsNullOrEmpty(pass.Rows[p + 1]["Date"].ToString()))
            {
              date = DateTime.Parse(pass.Rows[p]["Date"].ToString());
              passwordexpired = date.AddDays(60);
              password.SetAttribute("value", pass.Rows[p]["Pass"].ToString());
            }
            //if (passwordexpired > today && today >= date)
            //{
            //    password.SetAttribute("value", pass.Rows[p]["Pass"].ToString());
            //    break;
            //}
            //else if (passwordexpired < today)
            //{
            //    password.SetAttribute("value", pass.Rows[p -1]["Pass"].ToString());
            //}
          }
          
          HtmlElement signin = tridexp.Document.GetElementById("signin");
          signin.InvokeMember("click");
          loggin = true;
        }
        catch (Exception ex)
        {
          string subject = "1st attempt at login";
          ErrorHandling.ErrorEmail(subject, ex);
        }
      }
//      else if (tridexp.DocumentTitle == "Password Expired")
//      {
//        try
//        {
//          pass = contacts.Tables["p"];
//          int prow = pass.Rows.Count;
//          for (int p = 0; p < prow; p++)
//          {
//            int idpv = (int)pass.Rows[p]["idp"];
//            if (!String.IsNullOrEmpty(pass.Rows[p]["Date"].ToString()))
//            {
//              date = DateTime.Parse(pass.Rows[p]["Date"].ToString());
//            }
//            if (idpv == 12 && !String.IsNullOrEmpty(pass.Rows[p]["Date"].ToString()))
//            {
//              login(1, "we've reached the 12th pw that didn't reset to 1", today);
//            }
//            else if (String.IsNullOrEmpty(pass.Rows[p]["Date"].ToString()))
//            {
//              login(p, "this checks for empty date cell that caused this error", today);
//            }
//            else if (!String.IsNullOrEmpty(pass.Rows[p]["Date"].ToString()) && !String.IsNullOrEmpty(pass.Rows[p + 1]["Date"].ToString()) &&
//                     DateTime.Parse(pass.Rows[p]["Date"].ToString()) > DateTime.Parse(pass.Rows[p + 1]["Date"].ToString()))
//            {
//              login(p + 1, "after 12 pw's filled this should iterate through the 12 again caused this error", today);
//            }
//          }
//        }
//        catch (Exception ex)
//        {
//          string subject = "password expired error";
//          ErrorHandling.ErrorEmail(subject, ex);
//        }
//      }
      else if (loggin == true)
      {
        try
        {
          HtmlAgilityPack.HtmlDocument doc = new HtmlAgilityPack.HtmlDocument();
          HtmlNodeNavigator navigator = new HtmlNodeNavigator(tridexp.DocumentStream);
          //doc.Load(tridexp.DocumentStream);
          //HtmlNodeCollection rows = doc.DocumentNode.SelectNodes("//table[@id='VR_bfngn7tvg_1002789']//tr");
          //int columncount = rows[0].ChildNodes.Count / 2;
          //HtmlNodeCollection cells = doc.DocumentNode.SelectNodes("//table[@id='VR_bfngn7tvg_1002789']//tr//td");
          tridexp.Stop();
          //header(cells, columncount);
          UpdateDatabase( HTMLtoSQL(navigator), today);
          //tablebody(rows, cells, columncount, today);
        }
        catch (Exception ex)
        {
          string subject = "final login error";
          ErrorHandling.ErrorEmail(subject, ex);
        }
      }
    }
    
    private bool Inject_ExecuteJavaScript (System.Windows.Forms.HtmlDocument doc)
    {
      if (doc == null) return false;
      var head = doc.GetElementsByTagName(@"head")[0];
      if (head != null) {
        var script = doc.CreateElement(@"script");
        if (script == null) return false;
        string jscript =  @"function cleanWhitespace()
{
  var node = document.body;
  for (var i=0; i<node.childNodes.length; i++)
  {
    var child = node.childNodes[i];
    if(child.nodeType == 3 && !/\S/.test(child.nodeValue))
    {
      node.removeChild(child);
      i--;
    }
    if(child.nodeType == 1)
    {
      cleanWhitespace(child);
    }
  }
  return node;
}";
        script.SetAttribute("text",jscript);
        var scriptElement = head.AppendChild(script);
        //var result = sendJS(jscript);
        if (scriptElement == null) return false;
      }
      var results = doc.InvokeScript("cleanWhitespace");
      return true;
    }
    
    private string sendJS(string JScript) {
      object[] args = {JScript};
      return tridexp.Document.InvokeScript("eval",args).ToString();
    }

    private void login(int p, string subject, DateTime today)
    {
      try
      {
        HtmlAgilityPack.HtmlDocument log = new HtmlAgilityPack.HtmlDocument();
        log.Load(tridexp.DocumentStream);
        pass.Rows[p]["Date"] = Convert.ToString(today);
        HtmlElement password1 = tridexp.Document.GetElementById("Password1");
        password1.SetAttribute("value", pass.Rows[p]["Pass"].ToString());
        HtmlElement password2 = tridexp.Document.GetElementById("Password2");
        password2.SetAttribute("value", pass.Rows[p]["Pass"].ToString());
        HtmlElement ok = tridexp.Document.GetElementById("formBotButtons");
        
        connecttrident();
        updatemysqlp();
        disconnect();
        newpage = new Trident.trident();
        newpage.ShowDialog();
        //tridexp.AllowNavigation = true;
        //tridexp.Navigate(new Uri("https://wmt.quickbase.com/db/bfngn7tvg?a=q&qid=1002789&qrppg=1000"), false);
      }
      catch (Exception ex)
      {
        ErrorHandling.ErrorEmail(subject, ex);
      }
    }

    private void header(HtmlNodeCollection cells, int columncount)
    {
      try
      {
        for (int i = 1; i < columncount; i++)
        {
          int tc = i + 1;
          string headercell = cells[i].InnerText.ToString();
          headercell = headercell.Replace("\r\n", " ").Trim();

          if (i == 6 || i == 7)
          { continue; }

          //else if (i >= 8)
          //{ }

          string th = trident_data_in.Tables["trident"].Columns[tc].ColumnName.ToString();
          tracer.Trace(traceout, string.Format("QB header: {0} ~ RHA header: {1}",headercell, th));
          if (headercell != th) continue;//throw new Exception();
        }
      }
      catch (Exception ex)
      {
        string subject = "header error";
        ErrorHandling.ErrorEmail(subject, ex);
      }
    }

    private void tablebody(HtmlNodeCollection rows, HtmlNodeCollection cells, int columncount, DateTime today)
    {
      
      try
      {
        DataTable trid_table = trident.Tables["trident"];
        DataRow nr;
        int rc = rows.Count;
        int cs = 0;
        int ce = 0;
        for (int r = 3; r < rc; r++)
        {
          cs = ((r - 2) * columncount) + 2;
          ce = cs + columncount - 1;
          nr = trid_table.NewRow();
          nr[cs - cs + 1] = today;

          for (int c = cs; c < ce; c++)
          {
            int col = c - ((r - 2) * columncount);
            string cellstr = cells[c].InnerText.ToString().Trim();
            cellstr = cellstr.Replace("\r\n", " ");
            cellstr = Regex.Replace(cellstr, @"\s+", " ");
            if (cellstr.Contains("&amp;"))
            { cellstr = cellstr.Replace("&amp;", "&"); }
            if (cellstr == null)
            {
              nr[col] = null;
              continue;
            }
            if (cellstr.Contains("&nbsp;"))
            {
              cellstr = cellstr.Replace("&nbsp;", "");
              cellstr = Regex.Replace(cellstr, @"\s+", " ");
            }
            if (col == 103 || col == 104 || col == 105 || col == 117)
            {
              if (cellstr != " " && cellstr.Length > 999)
              {
                cellstr = cellstr.Substring(0, 998);
                nr[cells[col-1].InnerText.ToString().Trim()] = cellstr;
                continue;
              }
            }
            nr[cells[col-1].InnerText.ToString().Trim()] = cellstr;
          }
          trid_table.Rows.Add(nr);
        }
        connecttrident();
        updatemysqltrident(tricmd, trida);
        disconnect();
        Dispose();
        DataRow[] active = trid_table.Select(string.Format("rundate = '{0}' AND proj_status = '{1}'", today, "Active"));
        connectcontacts();
        loadcontacts();
        DataTable proj = contacts.Tables["projects"];
        addprojects(active, proj);
        contactsupdate(active, proj);
        updatemysqlprojects(proj);
        updatemysqlprojectcontacts();
        disconnect();
        Dispose();
      }
      catch (Exception ex)
      {
        string subject = "tablebody error";
        ErrorHandling.ErrorEmail(subject, ex);
      }
    }
    
    private List<Dictionary<string, string>> HTMLtoSQL (HtmlNodeNavigator navigator)
    {
      //At Doc root, move to table by "id" <table>
      navigator.MoveToId("VR_bfngn7tvg_1002789");
      //At the table, moves to <tbody> element
      //navigator.MoveToFirstChild();
      //if (navigator.IsEmptyElement) navigator.MoveToNext();
      //moves to first <tr> which contains the headers
      //navigator.MoveToFirstChild();
      List<string> Headers = new List<string>();
      //while (navigator.MoveToNext()) {
      //if (navigator.IsEmptyElement){navigator.MoveToNext();}
      var aRow = navigator.CurrentNode.SelectNodes(".//span[@class='ColumnHeading']");
      if (aRow == null)
        throw new NullReferenceException();
      //var node = aRow.FindFirst("span");
      foreach (var node in aRow) {
        if (node.GetAttributeValue("class", "null") == "ColumnHeading"){
          Headers.Add(node.InnerHtml);
        }
      }
      //}
      //moves to next <tr> whiich is a border row
      //navigator.MoveToParent();//moves to <tbody>
      //navigator.MoveToNext();if (navigator.IsEmptyElement) navigator.MoveToNext();//moves to header <tr>
      //navigator.MoveToNext();if (navigator.IsEmptyElement) navigator.MoveToNext();//moves to border <tr>
      List<List<string>> Rows = new List<List<string>>();
      //moves next down all of the table rows
      //while (navigator.MoveToNext()) {
      //  if (navigator.IsEmptyElement) navigator.MoveToNext();
      var dataRows = navigator.CurrentNode.SelectNodes(".//tr[@canview='true']");
      if (dataRows == null) throw new NullReferenceException();
      foreach (var data in dataRows) {
        aRow = data.SelectNodes(".//td[not(@class='icr')]");
        if (aRow == null) continue;
        var list = new List<string>();
        foreach (var node in aRow) {
          list.Add(node.InnerText
                   .Replace("&nbsp;", "")
                   .Replace("&amp;", "&")
                   .Replace("\r\n", " ")
                   .RegexReplace("\\s{2,}"," ")
                  );
        }
        Rows.Add(list);
      }
      // Rows is a List of Lists which have all of the row data
      // Headers is a List of the column headers
      // Iterate the Lists into a List of Dictionaries having Key/Value pairs
      // which hold the headers and data for each row
      var allData = new List<Dictionary<string, string>>();
      foreach (var row in Rows) {
        var dict = new Dictionary<string,string>();
        for (int i = 0; i < row.Count-1; i++) {
          dict.Add(Headers[i], row[i]);
        }
        allData.Add(dict);
      }
      return allData;
    }
    
    private void UpdateDatabase ( List<Dictionary<string, string>> data, DateTime today)
    {
      try
      {
        DataTable trid_table = trident.Tables["trident"];
        trid_table.TableNewRow += new DataTableNewRowEventHandler(trid_table_TableNewRow);
        DataRow nr;
        foreach (var row in data) {
          nr = trid_table.NewRow();
          nr["rundate"] = today;
          foreach (var line in row) {
            nr[line.Key] = line.Value;
            tracer.Trace(traceout, string.Format("Key: {0}  Value: {1}",line.Key, line.Value));
          }
          trid_table.Rows.Add(nr);
        }

        connecttrident();
        updatemysqltrident(tricmd, trida);
        disconnect();
        Dispose();
        DataRow[] active = trid_table.Select(string.Format("rundate = '{0}' AND proj_status = '{1}'", today, "Active"));
        connectcontacts();
        loadcontacts();
        DataTable proj = contacts.Tables["projects"];proj.AcceptChanges();
        addprojects(active, proj);
        contactsupdate(active, proj);
        updatemysqlprojects(proj);
        updatemysqlprojectcontacts();
        disconnect();
        Dispose();
      }
      catch (Exception ex)
      {
        string subject = "tablebody error";
        ErrorHandling.ErrorEmail(subject, ex);
      }
    }

    void trid_table_TableNewRow(object sender, DataTableNewRowEventArgs e)
    {
      tracer.Trace(traceout, "New Row: HasErrors?:");
      tracer.Trace(traceout, e.Row.HasErrors.ToString());
    }

    private void loadcontacts()
    {
      try
      {
        loadtables("projects");
        loadtables("job_roles");
        loadtables("contacts");
        loadtables("project_contacts");
        contacts.AcceptChanges();
      }
      catch (Exception ex)
      {
        string subject = "load contacts error";
        ErrorHandling.ErrorEmail(subject, ex);
      }
    }

    /// <summary>
    /// Adds new stores to the projects table
    /// </summary>
    /// <param name="active">Array of <see cref="System.Data.DataRow"/> coming from the current Quickbase Trident</param>
    /// <param name="proj">A <see cref="System.Data.DataTable"/> having the current "projects" table</param>
    private void addprojects(DataRow[] active, DataTable proj)
    {
      try
      {
        //proj = contacts.Tables["projects"];
        int actrow = active.Length;
        int ar;

        for (ar = 0; ar < actrow; ar++)
        {
          string acttridid = (string)active[ar]["id"];
          DataRow[] actproj = proj.Select(string.Format("trident_id = '{0}'", acttridid));
          int actprojl = actproj.Length;

          if (actprojl == 0) //if this comparison succeeds then the project isn't in the "projects" table
          {
            DataRow pnr = proj.NewRow();
            pnr["trident_id"] = active[ar]["trident_id"];
            pnr["str_num"] = active[ar]["str_num"];
            pnr["str_seq"] = active[ar]["str_seq"];
            pnr["str_city"] = active[ar]["city"];
            pnr["str_st"] = active[ar]["st"];
            proj.Rows.Add(pnr);
          }
          else
          {
            int strid = (int)actproj[0].ItemArray[0];
            DataRow er = proj.Rows.Find(strid);
            string test = (string)active[ar].ItemArray[2];
            int t = int.Parse(test);
            string test1 = (string)er["trident_id"];
            int t1 = int.Parse(test1);
            if (t == t1)
            {
              er["str_num"] = active[ar]["str_num"];
              er["str_seq"] = active[ar]["str_seq"];
              er["str_city"] = active[ar]["city"];
              er["str_st"] = active[ar]["st"];
            }
          }
        }
      }
      catch (Exception ex)
      {
        string subject = "loading new projects into projects table error";
        ErrorHandling.ErrorEmail(subject, ex);
      }
    }

    private void contactsupdate(DataRow[] active, DataTable proj)
    {
      try
      {
        contact = contacts.Tables["contacts"];
        DataColumn flname = contact.Columns.Add("fullname");
        DataTable temp = new DataTable();
        temp.Columns.Add("fullname");
        int sid = 0;
        int crc = contact.Rows.Count;
        int cr;

        for (cr = 0; cr < crc; cr++)
        {
          DataRow contactrow = contact.Rows[cr];
          contactrow["fullname"] = contactrow["first_name"] + " " + contactrow["last_name"];
        }

        projcont = contacts.Tables["project_contacts"];
        
        int actrow = active.Length;
        int[] jrids = { 60, 59, 86, 61, 87, 58, 57, 88, 56, 55, 89, 90, 94, 95 };
        int ar;

        for (ar = 0; ar < actrow; ar++)
        {
          string actstr = (string)active[ar]["str_num"];
          string actseq = (string)active[ar]["str_seq"];
          DataRow[] actproj = proj.Select(string.Format("str_num = '{0}' AND str_seq = '{1}'", actstr, actseq));
          int actprojl = actproj.Length;
          sid = (int)actproj[0]["str_id"];
          int index;
          int pos = 0;

          for (index = 89; index < 103; index++)
          {
            string activename = active[ar].ItemArray[index].ToString();
            int jrid = jrids[pos];
            pos++;
            string[] number = { "0", "1", "2", "3", "4", "5", "6", "7", "8", "9" };
                        if (activename.Contains("Diane"))
                        {}
                        activename = activename.Replace("'", @"''");
            if (String.IsNullOrEmpty(activename) || activename == " " || number.Any(s => activename.Contains(s)))
            { continue; }
            if (activename.Contains("JRADAMS"))
            { activename = "JR ADAMS"; }
            else if (activename.Contains("@"))
            {
              string[] email = activename.Split(new Char[] { '.', '@' });
              activename = email[0] + " " + email[1];
            }

            //DataRow[] pcarr = projcont.Select(string.Format("str_id = '{0}' AND job_role_id = '{1}'", sid, jrid));
            DataRow[] pcarr = projcont.Select("str_id =" + sid + " AND job_role_id =" + jrid);
            int pcrc = pcarr.Length;
            DataRow[] found = contact.Select(string.Format("fullname like '{0}'", activename));
            int fl = found.Length;
            if (fl < 1 && (!String.IsNullOrEmpty(activename) || activename != " "))
            {
              int trc = temp.Rows.Count;
              int tempr;
              if (trc == 0)
              {
                DataRow temprow = temp.NewRow();
                temprow["fullname"] = activename;
                temp.Rows.Add(temprow);
                mailman.sendmail("Missing Walmart Contact - " + activename + "", "" + activename +
                                 " with Walmart is missing in the contacts database. " +
                                 "Please add the name before tomorrow morning to avert another email.");
              }

              else if (trc > 0)
              {
                for (tempr = 0; tempr < trc; tempr++)
                {
                  if (temp.Rows[tempr].ItemArray[0].ToString() == activename)
                  {
                    tempr = trc;
                    break;
                  }

                  if (tempr == trc - 1)
                  {
                    DataRow temprow = temp.NewRow();
                    temprow["fullname"] = activename;
                    temp.Rows.Add(temprow);
                    mailman.sendmail("Missing Wal-Mart Contact - " + activename + "", "" + activename +
                                     " with Wal-Mart is missing in the contacts database. " +
                                     "Please add the name before tomorrow morning to avert another email.");
                  }
                }
              }
            }
            else if (pcrc == 1)
            {
              int cid = (int)found[0]["contact_id"];
              int pccid = (int)pcarr[0]["contact_id"];
              if (cid == pccid)
              {
                if (String.IsNullOrEmpty(pcarr[0]["timestamp"].ToString()) || pcarr[0]["timestamp"].ToString() == " " || String.IsNullOrEmpty(pcarr[0]["user"].ToString()) || pcarr[0]["user"].ToString() == " ")
                {
                  pcarr[0]["timestamp"] = DateTime.Now;
                  pcarr[0]["user"] = "trident_app";
                  continue;
                }
                else
                {
                  continue;
                }
              }
              else
              {
                pcarr[0]["contact_id"] = cid;
                pcarr[0]["timestamp"] = DateTime.Now;
                pcarr[0]["user"] = "trident_app";
              }
            }

            else if (pcrc == 0)
            {
              DataRow pcnr = projcont.NewRow();
              int cid = (int)found[0].ItemArray[0];
              pcnr["str_id"] = sid;
              pcnr["contact_id"] = cid;
              pcnr["job_role_id"] = jrid;
              pcnr["timestamp"] = DateTime.Now;
              pcnr["user"] = "trident_app";
              projcont.Rows.Add(pcnr);
            }
          }
        }
      }
      catch (Exception ex)
      {
        string subject = "updating contacts error";
        ErrorHandling.ErrorEmail(subject, ex);
      }
    }
    
    WebBrowser tridexp = new WebBrowser();
    public static trident newpage;
    MySqlConnection conn = new MySqlConnection();
    MySqlCommand tricmd = new MySqlCommand();
    MySqlDataAdapter trida = new MySqlDataAdapter();
    public static string _username;
    private string _qbusername;
    public static string _qbpass;
    private string _database;
    private int _port;
    bool loggin = false;
  }
}